package Database;

import Enclosure.*;

import javax.swing.plaf.basic.BasicInternalFrameTitlePane;
import java.sql.*;
import java.util.ArrayList;


public class ZooDataHandler implements IZooDataHandler {
    Connection connection;

    public ZooDataHandler() throws SQLException, ClassNotFoundException {
        Class.forName("org.sqlite.JDBC");
        connection = DriverManager.getConnection("jdbc:sqlite:C:/JavaProjects/Zoo/src/Database/Zoo.db");
    }


    @Override
    public ArrayList<Enclosure> getAllEnclosures() throws SQLException {
        ArrayList<Enclosure> enclosures = new ArrayList<Enclosure>();
        Statement statement = connection.createStatement();
        ResultSet enclosureResultSet = statement.executeQuery("Select e.enclosureId, e.enclosureName, e.enclosureSize, e.enclosureTypeId, et.enclosureTypeDesc, e.exhibitName, e.exhibitDesc, e.numEnclosureEntries, e.isEnclosureLightsOn, e.isEnclosureLocked, e.enclosureHasAnimal, e.enclosureHasPerson FROM Enclosure e, EnclosureType et WHERE e.enclosureTypeId = et.enclosureTypeId");

        while (enclosureResultSet.next()) {
            enclosures.add(new Enclosure(enclosureResultSet.getInt("enclosureId"), enclosureResultSet.getString("enclosureName"), enclosureResultSet.getInt("enclosureSize"), enclosureResultSet.getInt("enclosureTypeId"), enclosureResultSet.getString("enclosureTypeDesc"), enclosureResultSet.getString("exhibitName"), enclosureResultSet.getString("exhibitDesc"), enclosureResultSet.getInt("numEnclosureEntries"), enclosureResultSet.getBoolean("isEnclosureLightsOn"), enclosureResultSet.getBoolean("isEnclosureLocked"), enclosureResultSet.getBoolean("enclosureHasAnimal"), enclosureResultSet.getBoolean("enclosureHasPerson")));
        }

        for (Enclosure enclosure : enclosures) {
            ResultSet entryResultSet = statement.executeQuery("Select e.entryId, e.entryTypeId, et.entryTypeDesc, e.isEntryOpen from Entry e, EntryType et WHERE e.entryTypeId = et.entryTypeId AND e.enclosureId = " + enclosure.getEnclosureId());
            while (entryResultSet.next()) {
                enclosure.addEnclosureEntry(new Entry(entryResultSet.getInt("entryId"), entryResultSet.getInt("entryTypeId"), entryResultSet.getString("entryTypeDesc"), entryResultSet.getBoolean("isEntryOpen")));
            }
        }
        return enclosures;
    }


    @Override
    public Enclosure getEnclosure(int enclosureId) throws SQLException {
        Statement statement = connection.createStatement();
        ResultSet enclosureResultSet = statement.executeQuery("Select e.enclosureId, e.enclosureName, e.enclosureSize, e.enclosureTypeId, et.enclosureTypeDesc, e.exhibitName, e.exhibitDesc, e.numEnclosureEntries, e.isEnclosureLightsOn, e.isEnclosureLocked, e.enclosureHasAnimal, e.enclosureHasPerson FROM Enclosure e, EnclosureType et WHERE e.enclosureTypeId = et.enclosureTypeId AND e.enclosureId = " + enclosureId);
        enclosureResultSet.next();
        Enclosure enclosure = new Enclosure(enclosureResultSet.getInt("enclosureId"), enclosureResultSet.getString("enclosureName"), enclosureResultSet.getInt("enclosureSize"), enclosureResultSet.getInt("enclosureTypeId"), enclosureResultSet.getString("enclosureTypeDesc"), enclosureResultSet.getString("exhibitName"), enclosureResultSet.getString("exhibitDesc"), enclosureResultSet.getInt("numEnclosureEntries"), enclosureResultSet.getBoolean("isEnclosureLightsOn"), enclosureResultSet.getBoolean("isEnclosureLocked"), enclosureResultSet.getBoolean("enclosureHasAnimal"), enclosureResultSet.getBoolean("enclosureHasPerson"));

        ResultSet entryResultSet = statement.executeQuery("Select e.entryId, e.entryTypeId, et.entryTypeDesc, e.isEntryOpen from Entry e, EntryType et WHERE e.entryTypeId = et.entryTypeId AND e.enclosureId = " + enclosure.getEnclosureId());
        while (entryResultSet.next()) {
            enclosure.addEnclosureEntry(new Entry(entryResultSet.getInt("entryId"), entryResultSet.getInt("entryTypeId"), entryResultSet.getString("entryTypeDesc"), entryResultSet.getBoolean("isEntryOpen")));
        }
        return enclosure;
    }


    @Override
    public void addEnclosure(Enclosure enclosure) throws SQLException {
        Statement statement = connection.createStatement();
        statement.executeUpdate("INSERT INTO Enclosure VALUES (null, '" + enclosure.getEnclosureName() + "', " + enclosure.getEnclosureSize() + ", " + enclosure.getEnclosureTypeId() + ", '" + enclosure.getExhibitName() + "', '" + enclosure.getExhibitDesc() + "', " + enclosure.getNumEnclosureEntries() + ", " + (enclosure.isEnclosureLightsOn() ? 1 : 0) + ", " + (enclosure.isEnclosureLocked() ? 1 : 0) + ", " + (enclosure.isEnclosureOccupiedByAnimal() ? 1 : 0) + ", " + (enclosure.isEnclosureOccupiedByPerson() ? 1 : 0) + ")");

        ResultSet rs = statement.getGeneratedKeys();
        rs.next();
        String key = rs.getString(1);

        for (Entry entry : enclosure.getEnclosureEntries()) {
            statement.executeUpdate("INSERT INTO Entry VALUES (null, " + key + ", " + entry.getEntryTypeId() + ", 0)");
        }
    }


    @Override
    public void updateEnclosure(Enclosure enclosure) throws SQLException {
        Statement statement = connection.createStatement();
        statement.executeUpdate("UPDATE Enclosure SET enclosureName = '" + enclosure.getEnclosureName() + "', enclosureSize = " + enclosure.getEnclosureSize() + ", enclosureTypeId = " + enclosure.getEnclosureTypeId() + ", exhibitName = '" + enclosure.getExhibitName() + "', exhibitDesc = '" + enclosure.getExhibitDesc() + "', numEnclosureEntries = " + enclosure.getNumEnclosureEntries() + ", isEnclosureLightsOn = " + (enclosure.isEnclosureLightsOn() ? 1 : 0) + ", isEnclosureLocked = " + (enclosure.isEnclosureLocked() ? 1 : 0) + ", enclosureHasAnimal = " + (enclosure.isEnclosureOccupiedByAnimal() ? 1 : 0) + ", enclosureHasPerson = " + (enclosure.isEnclosureOccupiedByPerson() ? 1 : 0) + " WHERE EnclosureId = " + enclosure.getEnclosureId());

        statement.executeUpdate("DELETE FROM Entry WHERE enclosureId = " + enclosure.getEnclosureId());

        for (Entry entry : enclosure.getEnclosureEntries()) {
            System.out.println(enclosure.getEnclosureId() + " - " + entry.getEntryTypeId());
            statement.executeUpdate("INSERT INTO Entry VALUES (null, " + enclosure.getEnclosureId() + ", " + entry.getEntryTypeId() + ", " + (entry.isEntryOpen() ? 1 : 0) + ")");
        }
    }


    @Override
    public void deleteEnclosure(Enclosure enclosure) throws SQLException {
        Statement statement = connection.createStatement();
        statement.executeUpdate("DELETE FROM Enclosure WHERE enclosureId = " + enclosure.getEnclosureId());
        statement.executeUpdate("DELETE FROM Entry WHERE enclosureId = " + enclosure.getEnclosureId());
    }


    @Override
    public ArrayList<String[]> getEnclosureTypes() throws SQLException {
        ArrayList<String[]> enclosureTypes = new ArrayList<String[]>();
        Statement statement = connection.createStatement();
        ResultSet enclosureTypeResultSet = statement.executeQuery("Select et.enclosureTypeId, et.enclosureTypeDesc FROM EnclosureType et");
        while (enclosureTypeResultSet.next()) {
            enclosureTypes.add(new String[] {enclosureTypeResultSet.getString("enclosureTypeId"), enclosureTypeResultSet.getString("enclosureTypeDesc")});
        }
        return enclosureTypes;
    }


    public ArrayList<String[]> getEntryTypes() throws SQLException {
        ArrayList<String[]> entryTypes = new ArrayList<String[]>();
        Statement statement = connection.createStatement();
        ResultSet enclosureTypeResultSet = statement.executeQuery("Select e.entryTypeId, e.entryTypeDesc FROM EntryType e");
        while (enclosureTypeResultSet.next()) {
            entryTypes.add(new String[] {enclosureTypeResultSet.getString("entryTypeId"), enclosureTypeResultSet.getString("entryTypeDesc")});
        }
        return entryTypes;
    }
}